package dnl.persistence;

import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.LineNumberReader;
import java.io.Reader;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

import au.com.bytecode.opencsv.CSVReader;

public class CsvToJdbcImporter implements BatchPreparedStatementSetter {

    private static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(CsvToJdbcImporter.class);

    private JdbcTemplate jdbcTemplate;
    private int linesInCsv;

    private DataConverter dataConverter = new DataConverter();

    public CsvToJdbcImporter(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public CsvToJdbcImporter(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void importCsv(TableInfo exportedTableInfo, File csv) throws SQLException, IOException {
        try (LineNumberReader lnr = new LineNumberReader(new FileReader(csv))) {
            lnr.skip(Long.MAX_VALUE);
            importCsv(exportedTableInfo, new FileReader(csv), lnr.getLineNumber());
        }
    }

    public void importCsv(TableInfo exportedTableInfo, Reader csv, final int linesInCsv) throws SQLException, IOException {
        logger.debug("importCsv() :: "+exportedTableInfo.getTableName()+" :: start");
        this.linesInCsv = linesInCsv;
        JdbcTemplateToolz toolz = new JdbcTemplateToolz(jdbcTemplate);
        long t1 = System.currentTimeMillis();
        final int rowsAffected = jdbcTemplate.update("DELETE FROM " + exportedTableInfo.getTableName());
        long t2 = System.currentTimeMillis();
        logger.info("deleted {} rows in {} seconds", rowsAffected, (t2-t1)/1000);
        try (CSVReader csvReader = new CSVReader(csv)) {
            String insertSql = resolveInsertSql(exportedTableInfo);
            logger.debug(insertSql);
            //toolz.createTable(ti);
            final ColumnInfo[] columnInfos = exportedTableInfo.getColumnInfosAsArray();
            jdbcTemplate.batchUpdate(insertSql.toString(), new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    try {
                        String[] ln = csvReader.readNext();
                        if (ln == null) {
                            return;
                        }
                        for (int j = 0; j < ln.length; j++) {
                            Object obj = dataConverter.fromStringToDataObject(ln[j], columnInfos[j].getSqlType());
//                            if (logger.isDebugEnabled()) {
//                                logger.debug(ln[j] + "=" + obj);
//                            }
                            ps.setObject(j + 1, obj);
                        }
                    } catch (IOException e) {
                        throw new IllegalStateException(e);
                    }
                }

                @Override
                public int getBatchSize() {
                    return linesInCsv;
                }
            });
        }
        logger.debug("imported {} rows", linesInCsv);
        logger.debug("importCsv() :: "+exportedTableInfo.getTableName()+" :: end");
    }

    private String resolveInsertSql(TableInfo ti) {
        StringBuilder sb1 = new StringBuilder("INSERT INTO ");
        StringBuilder sb2 = new StringBuilder("(");
        sb1.append(ti.getTableName());
        sb1.append("(");
        String[] columnNames = ti.getColumnNames();
        boolean first = true;
        for (String cn : columnNames) {
            if (first) {
                first = false;
            } else {
                sb1.append(",");
                sb2.append(",");
            }
            sb1.append(cn);
            sb2.append("?");
        }
        sb1.append(")");
        sb2.append(")");

        sb1.append("VALUES");
        sb1.append(sb2.toString());
        return sb1.toString();
    }

    @Override
    public int getBatchSize() {
        return linesInCsv;
    }

    @Override
    public void setValues(PreparedStatement arg0, int arg1) throws SQLException {

    }

}
